package com.tencent.qplus.paddata.stat;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import javax.mail.MessagingException;

import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.tencent.qplus.paddata.db.DataSourceManager;
import com.tencent.qplus.paddata.util.ConfigUtil;
import com.tencent.qplus.paddata.util.JdbcUtils;
import com.tencent.qplus.paddata.util.MailUtils;

public class StatDataJob extends Job{
	
	private static Connection conn;
	private static Logger logger = Logger.getLogger(StatDataJob.class);
	
	private DecimalFormat percentDf = new DecimalFormat("#0.0%");
	DecimalFormat intFormat = new DecimalFormat("#,###");
	private StringBuilder mail = new StringBuilder();
	private static String lineDv = "======================================================================<br>\n";
	private int topNum = 50;
	private Long[] topDeviceId = new Long[topNum];
	private String mailExcelPath = "/data/java/paddatastat/datas/mailexcel/";
	private Set<String> specialFocusDeviceSet = new HashSet<String>();
	@Override
	public void run() throws SQLException, IOException, MessagingException {
		init();
		initSpecialFocusDivices();
		//判断是不是所有日志都已经解析完，通过完成的机器数来判断
		ResultSet rs = null;
		Statement st = null;
		String[] servers = ConfigUtil.getString("total_logservers").split(";");
		try {
			st = conn.createStatement();
			rs = st.executeQuery("select serverkey from logparsedone where statdate ='"+statDate+"'");
			Set<String> svrSet = new HashSet<String>();
			while(rs.next()){
				svrSet.add(rs.getString(1));
			}
			for(String s:servers){
				if(!svrSet.contains(s)){
					logger.info("server:"+s+"日志解析未完成...");
					return;
				}
			}
		} catch (SQLException e) {
			logger.error(e);
		}
		statUV(statDate);
		logger.info("statUV done...............");
		statUserDisByDevice(statDate);
		logger.info("statUserDisByDevice done...............");
		statMonthDefectionRate(statDate);
		logger.info("statMonthDefectionRate done...............");
		statDefectionRateBydevice(statDate);
		logger.info("statDefectionRateBydevice done...............");
		try {
			logger.info("发送邮件:"+mail.toString());
			MailUtils.sendHtmlEmailWithParts("pad数据日报("+statDate+")", mail.toString(),new String[]{getMailExcelFileName()}, ConfigUtil.getMailRecipientList("mail_recipient"),null,ConfigUtil.getMSender());
		} catch (UnsupportedEncodingException e) {
			logger.error(e);
			throw e;
		} catch (MessagingException e) {
			logger.error(e);
			throw e;
		}
		uninit();
	}

	@Override
	public void init() throws SQLException {
		try {
			conn = DataSourceManager.getInstance().getDataSource("paddatadb").getConnection();
		} catch (SQLException e) {
			logger.error(e);
			throw e;
		}
	}
	
	public void uninit(){
		JdbcUtils.release(conn);
		
	}
	
	public void initSpecialFocusDivices() throws SQLException{
		String sql = "select deviceId,deviceName from specialfocusdevices;";
		Statement st = null;
		ResultSet rs = null;
		
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			while(rs.next()){
				String deviceName = rs.getString(2);
				specialFocusDeviceSet.add(deviceName);
			}
		} catch (SQLException e) {
			throw e;
		}finally{
			JdbcUtils.release(rs);
			JdbcUtils.release(st);
		}
	}
	/**
	 * 计算30天UV数
	 * @param statDate
	 * @throws SQLException 
	 */
	private void statUV(String statDate) throws SQLException{
	    String beginDate = dateUtils.getDateStrByDaysFromDate(statDate, -30);
	    logger.info("statUV:"+statDate);
	    ResultSet rs = null;
		Statement st = null;
		int n=0;
		try {
			st = conn.createStatement();
			String sql = "";
			for (int i=0;i<100;i++){
				try {
					Thread.sleep(200);
				} catch (InterruptedException e) {
					e.printStackTrace();
				}
				sql = "SELECT COUNT(distinct uin) FROM uinlogin_"+i+" WHERE logindate>='"+ beginDate +"' and logindate<='"+statDate+"'";
				rs = st.executeQuery(sql);
				if(rs.next())
					n += rs.getInt(1);
				rs.close();
			}
		} catch (SQLException e) {
			logger.error(e);
			throw e;
		}
		mail.append("<br>").append(lineDv).append(beginDate).append("/").append(statDate).append("&nbsp;&nbsp;&nbsp;月用户数 UV:&nbsp;&nbsp;&nbsp;").append(intFormat.format(n)).append("\n");
		Statement resultSt = null;
		try {
			resultSt = conn.createStatement();
			resultSt.execute("REPLACE INTO uv(period,uv)VALUES('"+beginDate+"/"+statDate+"','"+n+"');");
		} catch (SQLException e) {
			logger.error(e);
			throw e;
		}finally{
			JdbcUtils.release(resultSt);
		}
	}
	
	
	/**
	 * 按设备计算30天UV数据分布
	 * @param statDate
	 * @throws SQLException 
	 */
	private  void statUserDisByDevice(String statDate) throws SQLException{
		logger.info("statUserDisByDevice:"+statDate);
	    StringBuilder sb = new StringBuilder();
	    StringBuilder mail2 = new StringBuilder();
	    String beginDate = dateUtils.getDateStrByDaysFromDate(statDate, -30);
	    String where = " where logindate>='"+beginDate+"' and logindate<='"+statDate+" 24'";
	    sb.append(" SELECT SUM(a.uinnum) as alluinnum ,a.deviceId,d.deviceName FROM(");
	    sb.append("SELECT COUNT(distinct uin) AS uinnum, deviceId FROM userinfo_0").append(where).append(" GROUP BY deviceId");
	    for (int i=1;i<100;i++){
	    	sb.append(" UNION SELECT COUNT(distinct uin) AS uinnum, deviceId FROM userinfo_")
	    	.append(i).append(where).append(" GROUP BY deviceId");
	    }
	    sb.append(") as a,devices as d where a.deviceId=d.id group by deviceId order by alluinnum desc");
	    ResultSet rs = null;
		Statement st = null;
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sb.toString());
			mail.append("<br>").append(lineDv).append(beginDate).append("/").append(statDate).append("&nbsp;&nbsp;&nbsp;设备的用户数，占比排行&nbsp;&nbsp;&nbsp;").append("<br>\n").append(lineDv);
			mail.append("<table border=\"1\"><tr><td>").append("设备名&nbsp;&nbsp;&nbsp;</td><td>UIN数&nbsp;&nbsp;&nbsp;</td><td>所占比例&nbsp;&nbsp;&nbsp;</td></tr>\n");
			
			mail2.append("<hr>").append("特别关注设备");
			mail2.append("<table border=\"1\"><tr><td>").append("设备名&nbsp;&nbsp;&nbsp;</td><td>UIN数&nbsp;&nbsp;&nbsp;</td><td>所占比例&nbsp;&nbsp;&nbsp;</td></tr>\n");
			
			List<UinDevice> list = new ArrayList<UinDevice>();
			int total=0;
			while(rs.next()){
				UinDevice ud = new UinDevice();
				ud.loginNum30 = rs.getInt(1);
				ud.deviceId = rs.getLong(2);
				ud.deviceName = rs.getString(3);
				total += ud.loginNum30;
				list.add(ud);
			}
			
			if(total>0){
				Statement resultSt = null;
				try {
					resultSt = conn.createStatement();
					for(int i=0;i<topNum;i++){
						UinDevice ud = list.get(i);
						topDeviceId[i] = ud.deviceId;
						mail.append(String.format("<tr><td>%s</td><td>%s</td><td>%s</td></tr>\n",ud.deviceName,intFormat.format(ud.loginNum30),percentDf.format(ud.loginNum30/(float)total)));
						resultSt.addBatch("REPLACE INTO deviceuv(period,deviceId,deviceName,uv,percent)" +
								"VALUES('"+beginDate+"/"+statDate+"','"+ud.deviceId+"','"+ud.deviceName+"','"+ud.loginNum30+"','"+percentDf.format(ud.loginNum30/(float)total)+"');");
					}
					for(UinDevice ud:list){
						if(specialFocusDeviceSet.contains(ud.deviceName))
							mail2.append(String.format("<tr><td>%s</td><td>%s</td><td>%s</td></tr>\n",ud.deviceName,intFormat.format(ud.loginNum30),percentDf.format(ud.loginNum30/(float)total)));
					}
					mail.append("</table>");
					mail2.append("</table>");
					mail.append(mail2);
					resultSt.executeBatch();
				} catch (SQLException e) {
					logger.error(e);
					throw e;
				}finally{
					JdbcUtils.release(resultSt);
				}
			}
		} catch (SQLException e) {
			logger.error(e);
			throw e;
		}
	}
	 
	/**
	 * 计算30天流失率
	 * @param statDate
	 * @throws SQLException 
	 */
	private void statMonthDefectionRate(String statDate) throws SQLException{
		logger.info("statMonthDefectionRate:"+statDate);
		mail.append("<br>").append(lineDv).append("&nbsp;&nbsp;&nbsp;30天总流失率&nbsp;&nbsp;&nbsp;").append("<br>").append(lineDv);
	    String pre30daysDate = dateUtils.getDateStrByDaysFromDate(statDate,-30);
	    String pre60daysDate =dateUtils.getDateStrByDaysFromDate(statDate,-60);
	    int num30 = 0;
	    int num60 = 0;
	    String where1 = " WHERE logindate>='"+ pre60daysDate +"' and logindate<'"+pre30daysDate+"' and uin not in(select distinct uin from uinlogin_";
	    String where11 = " where logindate>='"+ pre30daysDate +"' and logindate<='"+statDate+" 24')";
	    String where2 = " WHERE logindate>='"+ pre60daysDate +"' and logindate<'"+pre30daysDate+"'";
	    ResultSet rs = null;
		Statement st = null;
		try {
			st = conn.createStatement();
			for(int i=0;i<100;i++){
				try {
					Thread.sleep(200);
				} catch (InterruptedException e) {
					e.printStackTrace();
				}
				String sql1 = "SELECT COUNT(DISTINCT uin) FROM uinlogin_"+i+where1+i+where11;
				String sql2 = "SELECT COUNT(DISTINCT uin) FROM uinlogin_"+i+where2;
				rs = st.executeQuery(sql1);
				if(rs.next()){
					num30 += rs.getInt(1);
				}
				
				rs = st.executeQuery(sql2);
				if(rs.next()){
					num60 += rs.getInt(1);
				}
			}
		} catch (SQLException e) {
			logger.error(e);
			throw e;
		}
		mail.append("<table border=\"1\"><tr><td>").append(pre60daysDate).append("/").append(pre30daysDate).append("登录数</td><td>")
		.append(pre30daysDate).append("/").append("").append(statDate).append("流失数</td><td>流失率").append("</td></tr>\n");   
	    if (num60 != 0){
	        mail.append("<tr><td>").append(intFormat.format(num60)).append("</td><td>")
	        	.append(intFormat.format(num30)).append("</td><td>")
	        	.append(percentDf.format(num30/(float)num60)).append("</td></tr>\n");
	    }
	    mail.append("</table>");
	    Statement resultSt = null;
		try {
			resultSt = conn.createStatement();
			resultSt.execute("REPLACE INTO defectionrate(period,defectionNum,totalUin,defectionRate)" +
					"VALUES('"+pre60daysDate+"/"+pre30daysDate+"','"+num30+"','"+num60+"','"+percentDf.format(num30/(float)num60)+"');");
		} catch (SQLException e) {
			logger.error(e);
			throw e;
		}finally{
			JdbcUtils.release(resultSt);
		}
	}
	
	/**
	 * 按设备型号计算流失率
	 * @param statDate
	 * @throws SQLException 
	 * @throws IOException 
	 */
	private void statDefectionRateBydevice(String statDate) throws SQLException, IOException{
		logger.info("statDefectionRateBydevice:"+statDate);
		StringBuilder mail2 = new StringBuilder();
		mail.append("<br>").append(lineDv).append("&nbsp;&nbsp;&nbsp;各设备30天流失率&nbsp;&nbsp;&nbsp;").append("<br>\n").append(lineDv);
		mail2.append("<hr>").append("&nbsp;&nbsp;&nbsp;特别关注的设备30天流失率&nbsp;&nbsp;&nbsp;").append("<br>");
		
		String pre30daysDate = dateUtils.getDateStrByDaysFromDate(statDate,-30);
		String pre60daysDate = dateUtils.getDateStrByDaysFromDate(statDate,-60);
		Map<Long,UinDevice> deviceMap = new HashMap<Long,UinDevice>();
		String where1 = " WHERE logindate>='"+ pre60daysDate +"' and logindate<='"+pre30daysDate+"' and uin not in(select distinct uin from uinlogin_";
	    String where11 = " where logindate>='"+ pre30daysDate +"' and logindate<='"+statDate+" 24')";
	    String where2 = " WHERE logindate>='"+ pre60daysDate +"' and logindate<='"+pre30daysDate+"'";
	    ResultSet rs = null;
		Statement st = null;
		int totalDefection=0;
		for (int i=0;i<100;i++){
			StringBuilder sb = new StringBuilder();
			sb.append(" SELECT a.uinnum,a.deviceId,d.deviceName FROM(");
			sb.append("SELECT COUNT(distinct uin) AS uinnum, deviceId FROM userinfo_").append(i).append(where1).append(i).append(where11)
			.append(" GROUP BY deviceId) as a,devices as d where a.deviceId=d.id");
		    
		    
		    StringBuilder sb2 = new StringBuilder();
		    sb2.append(" SELECT a.uinnum,a.deviceId,d.deviceName FROM(");
		    sb2.append("SELECT COUNT(distinct uin) AS uinnum, deviceId FROM userinfo_").append(i).append(where2)
		    .append(" GROUP BY deviceId) as a,devices as d where a.deviceId=d.id");
		    
		    try {
				st = conn.createStatement();
				rs = st.executeQuery(sb.toString());
				while(rs.next()){
					int uinNum = rs.getInt(1);
					totalDefection += uinNum;
					Long deviceId = rs.getLong(2);
					String deviceName = rs.getString(3);
					if(deviceMap.containsKey(deviceId)){
						deviceMap.get(deviceId).loginNum30 += uinNum;
					}else{
						UinDevice ud = new UinDevice();
						ud.loginNum30 = uinNum;
						ud.deviceId = deviceId;
						ud.deviceName = deviceName;
						deviceMap.put(ud.deviceId, ud);
					}
				}
				rs = st.executeQuery(sb2.toString());
				while(rs.next()){
					int uinNum = rs.getInt(1);
					Long deviceId = rs.getLong(2);
					String deviceName = rs.getString(3);
					if(deviceMap.containsKey(deviceId)){
						deviceMap.get(deviceId).lingNum60 += uinNum;
					}else{
						UinDevice ud = new UinDevice();
						ud.lingNum60 = uinNum;
						ud.deviceId = deviceId;
						ud.deviceName = deviceName;
						deviceMap.put(ud.deviceId, ud);
					}
				}
		    
			} catch (SQLException e) {
				logger.error(e);
				throw e;
			}
	    }
		
	    mail.append("<table border=\"1\"><tr><td>").append("设备型号</td><td>")
	    .append(pre60daysDate).append("/").append(pre30daysDate).append("登录数</td><td>")
		.append(pre30daysDate).append("/").append("").append(statDate).append("流失数</td><td>流失率&nbsp;&nbsp;&nbsp;&nbsp;</td><td>占总流失比例").append("</td></tr>\n"); 
	    
	    mail2.append("<table border=\"1\"><tr><td>").append("设备型号</td><td>")
	    .append(pre60daysDate).append("/").append(pre30daysDate).append("登录数</td><td>")
		.append(pre30daysDate).append("/").append("").append(statDate).append("流失数</td><td>流失率&nbsp;&nbsp;&nbsp;&nbsp;</td><td>占总流失比例").append("</td></tr>\n"); 
	    
	    //写Excel
	    Workbook wb = new XSSFWorkbook();
	    Sheet sheet = wb.createSheet("第一页");
	    sheet.setColumnWidth(0, 256*30);
	    Row row0 = sheet.createRow(0);
	    Cell cell = row0.createCell(0);
	    cell.setCellValue("各设备流失率("+this.statDate+")");
	    Font font = wb.createFont();
	    font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
	    font.setFontHeightInPoints((short) 24);
	    CellStyle cellStyle = wb.createCellStyle();
	    cellStyle.setFont(font);
	    cell.setCellStyle(cellStyle);
	    
	    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));
	    Row row1 = sheet.createRow(1);
	    row1.setHeightInPoints(20);
	    row1.createCell(0).setCellValue("设备型号");
	    row1.createCell(1).setCellValue("登录数");
	    row1.createCell(2).setCellValue("流失数");
	    row1.createCell(3).setCellValue("流失率");
	    row1.createCell(4).setCellValue("占总流失比例");
	    
	    DataFormat format1 = wb.createDataFormat();
	    CellStyle cellStyle1 = wb.createCellStyle();
	    cellStyle1.setDataFormat(format1.getFormat("#,###"));
	    
	    DataFormat format2 = wb.createDataFormat();
	    CellStyle cellStyle2 = wb.createCellStyle();
	    cellStyle2.setDataFormat(format2.getFormat("0.0%"));
	    
	    
	    Statement resultSt = null;
		try {
			resultSt = conn.createStatement();
			//LinkedList<UinDevice> list = new LinkedList<UinDevice>();
			int rowNum=2;
		    for(Long deviceId:topDeviceId){
		    	UinDevice ud = deviceMap.get(deviceId);
		    	
		    	if(ud!=null){
//		    		if(list.size()==0)list.add(ud);
//		    		int i=0;
//		    		int size = list.size();
//		    		for(i=0;i<size;i++){
//		    			UinDevice temp = list.get(i);
//		    			if(temp.lingNum60!=0 && ud.lingNum60!=0 && (temp.loginNum30/(float)temp.lingNum60)<=(ud.loginNum30/(float)ud.lingNum60)){
//		    				list.add(i,ud);
//		    				break;
//		    			}
//		    		}
//		    		if(i==size){
//		    			list.addLast(ud);
//		    		}
		    		
		    		mail.append("<tr><td>").append(ud.deviceName).append("</td><td>")
	    			.append(intFormat.format(ud.lingNum60)).append("</td><td>")
		    		.append(intFormat.format(ud.loginNum30)).append("</td><td>")
		    		.append(ud.lingNum60>0?percentDf.format(ud.loginNum30/(float)ud.lingNum60):"0").append("</td><td>")
		    		.append(totalDefection>0 ? percentDf.format(ud.loginNum30/(float)totalDefection):"0").append("</td></tr>\n");
		    		
		    		Row row = sheet.createRow(rowNum++);
		    		row.createCell(0).setCellValue(ud.deviceName);
		    		cell = row.createCell(1);
		    		cell.setCellValue(ud.lingNum60);
		    		cell.setCellStyle(cellStyle1);
		    		
		    		cell = row.createCell(2);
		    		cell.setCellValue(ud.loginNum30);
		    		cell.setCellStyle(cellStyle1);
		    		
		    		cell = row.createCell(3);
		    		cell.setCellValue(ud.lingNum60>0?ud.loginNum30/(float)ud.lingNum60:0);
		    		cell.setCellStyle(cellStyle2);
		    		
		    		cell = row.createCell(4);
		    		cell.setCellValue(totalDefection>0 ? ud.loginNum30/(float)totalDefection:0);
		    		cell.setCellStyle(cellStyle2);
		    		
		    		resultSt.addBatch("REPLACE INTO devicedefectionrate(period,deviceId,deviceName,defectionNum,totalUin,defectionRate,percent)" +
		    				"VALUES('"+pre60daysDate+"/"+pre30daysDate+"','"+deviceId+"','"+ud.deviceName+"','"
		    				+ud.loginNum30+"','"+ud.lingNum60+"','"
		    				+(ud.lingNum60>0?percentDf.format(ud.loginNum30/(float)ud.lingNum60):"0")+"','"
		    				+(totalDefection>0 ? percentDf.format(ud.loginNum30/(float)totalDefection):"0")+"');");
			    	}
			}
//		    for(UinDevice ud:list){
//		    	mail.append("<tr><td>").append(ud.deviceName).append("</td><td>")
//    			.append(intFormat.format(ud.lingNum60)).append("</td><td>")
//	    		.append(intFormat.format(ud.loginNum30)).append("</td><td>")
//	    		.append(ud.lingNum60>0?percentDf.format(ud.loginNum30/(float)ud.lingNum60):"0").append("</td><td>")
//	    		.append(totalDefection>0 ? percentDf.format(ud.loginNum30/(float)totalDefection):"0").append("</td></tr>\n");
//		    }
		    Set<Entry<Long, UinDevice>> uds = deviceMap.entrySet();
    		for(Entry<Long, UinDevice> entry:uds){
    			UinDevice ud = entry.getValue();
	    		if(specialFocusDeviceSet.contains(ud.deviceName)){
	    			mail2.append("<tr><td>").append(ud.deviceName).append("</td><td>")
	    			.append(intFormat.format(ud.lingNum60)).append("</td><td>")
		    		.append(intFormat.format(ud.loginNum30)).append("</td><td>")
		    		.append(ud.lingNum60>0?percentDf.format(ud.loginNum30/(float)ud.lingNum60):"0").append("</td><td>")
		    		.append(totalDefection>0 ? percentDf.format(ud.loginNum30/(float)totalDefection):"0").append("</td></tr>\n");
	    		}
    		}
		    mail.append("</table>");
		    mail2.append("</table>");
		    mail.append(mail2);
			resultSt.executeBatch();
			FileOutputStream fileOut = new FileOutputStream(getMailExcelFileName());
			wb.write(fileOut);
			fileOut.close();
		} catch (SQLException e) {
			logger.error(e);
			throw e;
		} catch (FileNotFoundException e) {
			logger.error(e);
			throw e;
		} catch (IOException e) {
			logger.error(e);
			throw e;
		}finally{
			JdbcUtils.release(resultSt);
		}
	}
	private String getMailExcelFileName(){
		return mailExcelPath+"data"+this.statDate+".xlsx";
	}
	class UinDevice{
		public Long deviceId;
		public String deviceName;
		public int loginNum30;
		public int lingNum60;
		public float defectionRate;
	}
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		StatDataJob job = new StatDataJob();
		job.stat(args);
		//job.test();

	}
}
